package org.springcloud.wisdom.spring_02_classroom.student.sschedule.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springcloud.wisdom.spring_02_classroom.admin.createcourse.entity.Course;
import org.springcloud.wisdom.spring_02_classroom.student.sschedule.entity.Student;

import java.util.List;

@Mapper
public interface StudentScheduleDao{
    //学生查看自己名下课程
    @Select("SELECT c.cid, c.cname, t.tname " +
            "AS tname, c.ltime, c.location, c.chour " +
            "FROM " +
            "  course AS c " +
            "JOIN " +
            "  choose AS ch ON c.cid = ch.cid " +
            "LEFT JOIN " +
            "  teacher AS t ON c.tid = t.tid " +
            "WHERE " +
            "  ch.sid = #{sid} AND c.isdel=0")
    List<Course> selectCoursesByTid(@Param("sid") String sid);

    //学生查看所属课程的同学名单
    @Select("SELECT s.sid AS sid, s.sname AS sname, s.gender AS gender, " +
            "d.deptname AS deptname " +
            "FROM student s " +
            "INNER JOIN department d ON s.dept = d.deptid " +
            "INNER JOIN choose c ON s.sid = c.sid " +
            "WHERE c.cid = #{cid} " +
            "ORDER BY d.deptid") // 添加ORDER BY子句按deptid排序
    List<Student> selectCourseStudent(@Param("cid") String cid);


    //往年课程
    @Select("<script>" +
            "SELECT c.cid, c.cname, t.tname " +
            "AS tname, c.chour " +
            "FROM " +
            "  course AS c " +
            "JOIN " +
            "  choose AS ch ON c.cid = ch.cid " +
            "LEFT JOIN " +
            "  teacher AS t ON c.tid = t.tid " +
            "<where>" +
            "  ch.sid = #{sid} AND c.isdel=1 " +
            "</where>" +
            "LIMIT #{start}, #{size} "+
            "</script>")
    public List<Course> getcoursehistory(int start, int size, String sid );

    @Select("<script>" +
            "SELECT count(*) FROM " +
            "  course AS c " +
            "JOIN " +
            "  choose AS ch ON c.cid = ch.cid " +
            "LEFT JOIN " +
            "  teacher AS t ON c.tid = t.tid " +
            "<where>" +
            "  ch.sid = #{sid} AND c.isdel=1 " +
            "</where>" +
            "</script>")
    public int Countcoursehistory(  String sid  );

}
